from flask import Flask, jsonify, request
import mysql.connector
from mysql.connector import Error

# 配置数据库连接
db_config = {
    'host': 'localhost',       # 数据库主机地址
    'user': 'root',            # 数据库用户名
    'password': '123456',      # 数据库密码
    'database': 'novels',      # 数据库名称
    'charset': 'utf8mb4',      # 确保使用正确的字符集
    'collation': 'utf8mb4_unicode_ci',
    'use_unicode': True        # 强制启用Unicode支持
}

# 创建 Flask 应用
app = Flask(__name__)

# 创建数据库连接函数
def get_db_connection():
    try:
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None

# 查询所有书籍数据的接口
@app.route('/novels', methods=['GET'])
def get_novels():
    connection = get_db_connection()
    if connection is None:
        return jsonify({"error": "Database connection failed"}), 500

    try:
        cursor = connection.cursor(dictionary=True)

        # 从数据库查询所有小说
        cursor.execute("SELECT * FROM collection_of_novels")
        novels = cursor.fetchall()

        cursor.close()
        connection.close()

        return jsonify(novels), 200

    except Error as e:
        return jsonify({"error": str(e)}), 500


# 根据书名查询数据的接口
@app.route('/novels/search', methods=['GET'])
def search_novel():
    title = request.args.get('title')
    if not title:
        return jsonify({"error": "Title parameter is required"}), 400

    connection = get_db_connection()
    if connection is None:
        return jsonify({"error": "Database connection failed"}), 500

    try:
        cursor = connection.cursor(dictionary=True)

        # 防止 SQL 注入，使用参数化查询
        query = "SELECT * FROM collection_of_novels WHERE title LIKE %s"
        cursor.execute(query, ('%' + title + '%',))

        novels = cursor.fetchall()

        cursor.close()
        connection.close()

        if novels:
            return jsonify(novels), 200
        else:
            return jsonify({"message": "No novels found with the given title"}), 404

    except Error as e:
        return jsonify({"error": str(e)}), 500

@app.route('/novels/category_summary', methods=['GET'])
def get_category_summary():
    connection = get_db_connection()
    if connection is None:
        return jsonify({"error": "Database connection failed"}), 500

    try:
        cursor = connection.cursor(dictionary=True)

        # 查询按分类汇总的收藏数和真实粉丝数，并取前10个分类
        query = """
       SELECT category, 
               SUM(favorites_count) AS total_favorites,
               SUM(real_fans_count) AS total_real_fans
        FROM collection_of_novels
        GROUP BY category
        ORDER BY (SUM(favorites_count) + SUM(real_fans_count)) DESC
        LIMIT 7
        """
        cursor.execute(query)
        categories = cursor.fetchall()

        cursor.close()
        connection.close()

        return jsonify(categories), 200

    except Error as e:
        return jsonify({"error": str(e)}), 500

# 获取用于饼图的数据的接口
@app.route('/novels/pie_chart_data', methods=['GET'])
def get_pie_chart_data():
    connection = get_db_connection()
    if connection is None:
        return jsonify({"error": "Database connection failed"}), 500

    try:
        cursor = connection.cursor(dictionary=True)

        # 查询每个分类的总收藏数
        query = """
        SELECT category, 
               SUM(favorites_count) AS total_favorites
        FROM collection_of_novels
        GROUP BY category
        limit 6
        """
        cursor.execute(query)
        pie_chart_data = cursor.fetchall()

        cursor.close()
        connection.close()

        return jsonify(pie_chart_data), 200

    except Error as e:
        return jsonify({"error": str(e)}), 500

# 确保响应头包含正确的字符集声明
@app.after_request
def after_request(response):
    response.headers["Content-Type"] = "application/json; charset=utf-8"
    return response

# 启动应用
if __name__ == '__main__':
    app.run(debug=True)